package Command;

import java.util.Hashtable;
import java.util.Map;
import java.util.Vector;

import DTO.Field;
import DTO.Row;
import XML.XMLHelper;


public class Insert extends Command {
	
	public Insert(String str)
	{
		super(str);
	}
	 
	/**
	 * Execute query insert. Syntax :
	 * INSERT INTO table_name
	 * VALUES (value1, value2, value3,...) or
	 * INSERT INTO table_name (column1, column2, column3,...)
	 * VALUES (value1, value2, value3,...)
	 * @return 
	 * Return row which inserted if success
	 * Return null if failed!
	 */	
	
	public Object executeQuery()
	{
		Object ret = null;
		try
		{
			String sql = getSql();
			
			// Check insert keyword				
			String str1 = sql.trim();
			if(str1.toLowerCase().startsWith("insert"))
			{
				// Check into command
				int intoIndex = sql.toLowerCase().indexOf("insert") + "insert".length();
				String str2 = sql.substring(intoIndex).trim();			
				if(str2.toLowerCase().startsWith("into"))
				{
					int tableIndex = sql.toLowerCase().indexOf("into") + "into".length();
					String str3 = sql.substring(tableIndex).trim();
					String[] strs = str3.split(" ", 2);				
					String tableName = strs[0];
					
					ret = insert(ret, strs, tableName);				
				}
			}
			
			if (ret == null)
			{
				ret = "Insert failed !";
			}
			
			return ret;		
		}
		catch (Exception e) {
			// TODO: handle exception
			ret = "Insert failed !";
			return ret;
		}		
	}

	/**
	 * Insert row to database. It will use insertType2 or insertType1 method to insert
	 * @param ret Array of value
	 * @param strs String after into keywords
	 * @return Array of value
	 */
	private Object insert(Object ret, String[] strs, String tableName) {
		String str4 = "";
		ret = null;
		if(strs.length > 1)
		{
			str4 = strs[1].trim();
			// Insert type 1
			Object[] values = insertType1(str4);
			Object[] fields = null;
			Vector<Field> fullField = XMLHelper.GetListOfField(getDbName(), tableName);
			if(values != null)
			{
				if(fullField == null)
				{
					return ret;
				}
				
				if(values.length == fullField.size())
				{
					ret = values;
					//write to db
					Map<String, Object> row = new Hashtable<String, Object>();
					for(int i = 0 ; i < fullField.size(); i++)
					{
						int fieldType = fullField.get(i).get_Type();
						
						if(String.valueOf(values[i]).contains("'") && fieldType == 1)
						{
							String str = String.valueOf(values[i]);
							str = str.replaceAll("'", " ");
							str = str.trim();
							str = str.replaceAll(" +$", "");
							values[i] = str;
						}
						
						row.put(fullField.get(i).get_Name(), values[i]);						
					}
					
					ret = XMLHelper.InsertRow(getDbName(), tableName, new Row(row, false)) ? 
							"Insert 1 row successful!" : "Insert failed!";
				}
			}
			
			if(values == null)
			{
				System.out.println("Entering Type 2");
				fields = getFieldsForType2(str4);
				// Insert type 2
				values = insertType2(str4, fields);				
				
			
				if(values != null && fields != null)
				{
					System.out.println("Processing Type 2");
					ret = values;							
					// write to db
					Map<String, Object> row = new Hashtable<String, Object>();
					for(int i = 0 ; i < fullField.size(); i++)
					{	
						for(int j = 0 ; j < fields.length; j++)
						{							
							if(fields[j].equals(fullField.get(i).get_Name()))
							{
								int fieldType = fullField.get(i).get_Type();
								
								if(String.valueOf(values[j]).contains("'") && fieldType == 1)
								{
									String str = String.valueOf(values[j]);
									str = str.replaceAll("'", " ");
									str = str.trim();
									str = str.replaceAll(" +$", "");
									values[j] = str;
								}
								
								row.put(fullField.get(i).get_Name(), values[j]);
							}
							
						}
					}
					
					for(int i = 0 ; i < fullField.size(); i++)
					{
						if(!row.containsKey(fullField.get(i).get_Name()))
						{
							row.put(fullField.get(i).get_Name(), "null");
						}
					}
					
					ret = XMLHelper.InsertRow(getDbName(), tableName, new Row(row, false)) ? 	
							"Insert 1 row successful!" : "Insert failed!";
				}
			}
			
		}
		
		return ret;
	}
	
	private Object[] getFieldsForType2(String str)
	{
		String field = str.substring(str.indexOf("(") + 1, str.indexOf(")"));
		
		System.out.println("Field : " + field);
		Object[] fields = field.split(",").clone();
		
		for(int i = 0 ; i < fields.length; i++)
		{
			fields[i] = String.valueOf(fields[i]).trim();
		}
		
		return fields;
	}
	/**
	 * Execute query insert. Syntax :
	 * INSERT INTO table_name (column1, column2, column3,...)
	 * VALUES (value1, value2, value3,...)
	 * @param fields out fields of values which insert. If fields does not match with values
	 * , it will return null!
	 * @return 
	 * Return Array of value
	 * Return null if failed!
	 */
	private Object[] insertType2(String str, Object[] fields)
	{
		Object[] ret = null;
		//String field = str.substring(str.indexOf("(") + 1, str.indexOf(")"));		
		String str2 = str.substring(str.indexOf(")") + 1).trim();
		String[] values = null;
		if(str2.toLowerCase().startsWith("values"))
		{
			int strValueIndex = str2.toLowerCase().indexOf("values") + "values".length();
			String value = str2.substring(strValueIndex).trim();
			if(value.startsWith("(") && value.endsWith(")"))
			{
				value = value.substring(value.indexOf("(") + 1, value.indexOf(")"));
				values = value.split(",");
				for(int i = 0 ; i < values.length; i++)
				{
					values[i] = values[i].trim();
					System.out.println("Value : " + values[i]);
				} 
				
				ret = values;
			}			
		}
		
		if (values != null && fields != null && values.length > 0 && fields.length > 0)
		{
			if(values.length != fields.length)			
			{
				fields = null;
				ret = null;
			}
		}
		
		for(int i = 0 ; i < fields.length; i++)
		{
			System.out.println("Field " + fields[i]);
		}	
		
		return ret;
	}
	
	/**
	 * Execute query insert. Syntax :
	 * INSERT INTO table_name 
	 * VALUES (value1, value2, value3,...)
	 * @param str String : VALUES (value1, value2, value3,...)
	 * @return 
	 * Return Array of value
	 * Return null if failed!
	 */
	private Object[] insertType1(String str)
	{
		Object[] ret = null;
		if(str.toLowerCase().startsWith("values"))
		{
			int strValueIndex = str.toLowerCase().indexOf("values") + "values".length();
			String value = str.substring(strValueIndex).trim();
			if(value.startsWith("(") && value.endsWith(")"))
			{
				value = value.substring(value.indexOf("(") + 1, value.indexOf(")"));
				String[] values = value.split(",");
				for(int i = 0 ; i < values.length; i++)
				{
					values[i] = values[i].trim();
				}
				
				ret = values;
			}			
		}
		
		return ret;
	}
}
